from datetime import datetime

from flask import Flask, request, jsonify, make_response
from flask_restful import Api, Resource
from flask_cors import CORS
from flask_marshmallow import Marshmallow
from sqlalchemy import null, text

from extensions import db, cors
from flask_migrate import Migrate
from models import Manager, User, Book, Borrow, Fine, Information, OnlineBook, \
    BookNote, UserConsult, BookImage, UserBookBorrow, UserBook
import redis
import config

app = Flask(__name__)
# 绑定配置文件
app.config.from_object(config)
# restful API 接口格式
api = Api(app, default_mediatype="application/json")
# 初始化数据库
db.init_app(app)
# 可迁移数据库表单
migrate = Migrate(app, db)
# 数据格式转换
ma = Marshmallow(app)
# 跨域请求
cors.init_app(app)
CORS(app, resources=r'/*')
# redis连接
r = redis.StrictRedis(host="127.0.0.1", port=6379, db=0)
headers = {
    'typ': 'jwt',  # 登录信息认证
    'alg': 'HS256'
}
SALT = 'iv%i6xo7l8_t9bf_u!8#g#m*)*+ej@bek6)(@u3kh*42+unjv='


@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


# 数据库Schema

class UserSchema(ma.Schema):
    class Meta:
        fields = ("id", "username", "password", "studentnum", "email", "status")


user_schema = UserSchema()
users_schema = UserSchema(many=True)


class ManagerSchema(ma.Schema):
    class Meta:
        fields = ("id", "managername", "password", "email")


manager_schema = ManagerSchema()
managers_schema = ManagerSchema(many=True)


class BookSchema(ma.Schema):
    class Meta:
        fields = ("id", "bookname", "image", "author", "kind", "description", "press", "date", "inventory")


book_schema = BookSchema()
books_schema = BookSchema(many=True)


class BorrowSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "bookid", "quantity", "status", "borrowtime", "returntime")


borrow_schema = BorrowSchema()
borrows_schema = BorrowSchema(many=True)


class FineSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "bookid", "money", "borrowid", "status")


fine_schema = FineSchema()
fines_schema = FineSchema(many=True)


class InformationSchema(ma.Schema):
    class Meta:
        fields = ("id", "managerid", "uploadtime", "message")


information_schema = InformationSchema()
informations_schema = InformationSchema(many=True)


class OnlineBookSchema(ma.Schema):
    class Meta:
        fields = ("id", "bookname", "image", "author", "kind", "description", "press", "date", "text")


onlinebook_schema = OnlineBookSchema()
onlinebooks_schema = OnlineBookSchema(many=True)


class BookNoteSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "bookid", "note", "notetime")


booknote_schema = BookNoteSchema()
booknotes_schema = BookNoteSchema(many=True)


class UserConsultSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "consult", "answer", "consulttime")


userconsult_schema = UserConsultSchema()
userconsults_schema = UserConsultSchema(many=True)


class BookImageSchema(ma.Schema):
    class Meta:
        fields = ("id", "bookid", "image")


bookImage_schema = BookImageSchema()
bookImages_schema = BookImageSchema(many=True)


class UserBookBorrowSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "borrowuserid", "bookname", "author", "kind", "status", "borrowtime", "returntime")


userBookBorrow_schema = UserBookBorrowSchema()
userBookBorrows_schema = UserBookBorrowSchema(many=True)


class UserBookSchema(ma.Schema):
    class Meta:
        fields = ("id", "userid", "bookname", "author", "kind", "inventory")


userBook_schema = UserBookSchema()
userBooks_schema = UserBookSchema(many=True)


# -------------------------管理员端接口-------------------------------
# 管理员登录
# 登录
class ManagerLogin(Resource):
    def post(self):
        login_name = request.form.get('managername')
        login_password = request.form.get('password')
        print(login_name + "," + login_password)
        user = User.query.filter(User.username == login_name).first()
        manager = Manager.query.filter(Manager.managername == login_name).first()
        if user is not None:
            return {"code": 500,
                    "msg": "您没有管理员权限"}
        if manager is not None:
            if login_password == manager.password:
                return {"code": 200,
                        "msg": "管理员登陆成功"}
            else:
                return {"code": 500,
                        "msg": "管理员密码错误"}


api.add_resource(ManagerLogin, '/manager/managerLogin')


# ---------------------------图书管理---------------------------------
# 获取所有图书信息
class GetAllBooks(Resource):
    def get(self):  # get请求
        book_all = Book.query.all()
        return books_schema.dump(book_all)


api.add_resource(GetAllBooks, '/manager/allBooks')


# 根据类别获取该类别所有图书信息
class GetBooksByKind(Resource):
    def get(self, kind):  # get请求
        book_all = Book.query.filter(Book.kind == kind).all()
        return books_schema.dump(book_all)


api.add_resource(GetBooksByKind, '/manager/allBooksByKind/<string:kind>')


# 模糊搜索返回所有符合要求的书籍
class SearchBooks(Resource):
    def get(self, key):  # get请求
        book_all = Book.query.filter(Book.bookname.like("%" + key + "%")).all()
        return books_schema.dump(book_all)


api.add_resource(SearchBooks, '/manager/searchBooks/<string:key>')


# 新增图书
class AddBook(Resource):
    def post(self):

        add_bookname = request.form.get('bookname')
        add_author = request.form.get('author')
        add_kind = request.form.get('kind')
        add_description = request.form.get('description')
        add_press = request.form.get('press')
        add_date = request.form.get('date')
        add_inventory = request.form.get('inventory')
        add_image = request.form.get('image')

        add_book = Book(

            bookname=add_bookname,
            author=add_author,
            kind=add_kind,
            description=add_description,
            press=add_press,
            date=add_date,
            inventory=add_inventory,
            image=add_image
        )

        print(add_book)

        try:
            db.session.add(add_book)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "图书添加失败，数据存在空值或不合法"}
        return {"code": 200,
                "msg": "成功添加图书"}


api.add_resource(AddBook, '/manager/addBook')


# 删除图书
class DeleteBook(Resource):
    def get(self):
        delete_bookid = request.args.get('bookid')
        delete_book = Book.query.filter(Book.id == delete_bookid).first()
        try:
            db.session.delete(delete_book)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '图书删除失败，不存在该图书'}
        return {"code": 200,
                'msg': '图书删除成功'}


api.add_resource(DeleteBook, '/manager/deleteBook')


# 更新图书信息
class UpdateBook(Resource):
    def post(self):
        update_bookid = request.form.get('id')
        update_bookname = request.form.get('bookname')
        update_author = request.form.get('author')
        update_kind = request.form.get('kind')
        update_description = request.form.get('description')
        update_press = request.form.get('press')
        update_date = request.form.get('date')
        update_inventory = request.form.get('inventory')
        update_image = request.form.get('image')
        update_book = Book.query.filter(Book.id == update_bookid).first()
        try:
            update_book.bookname = update_bookname
            update_book.author = update_author
            update_book.kind = update_kind
            update_book.description = update_description
            update_book.press = update_press
            update_book.date = update_date
            update_book.inventory = update_inventory
            update_book.image = update_image
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "图书信息更新失败，图书不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新图书信息"}


api.add_resource(UpdateBook, '/manager/updateBook')


# 获取借阅该书籍的所有用户
class GetBorrowBookUsers(Resource):
    def get(self, bookid):
        book = Book.query.filter(Book.id == bookid).first()
        borrow_users = book.borrowusers
        return borrows_schema.dump(borrow_users)


api.add_resource(GetBorrowBookUsers, '/manager/borrowBookUsers/<int:bookid>')


# ---------------------------用户管理---------------------------------
# 获取所有用户信息
class GetAllUsers(Resource):
    def get(self):  # get请求
        user_all = User.query.all()
        return users_schema.dump(user_all)


api.add_resource(GetAllUsers, '/manager/allUsers')


# 根据用户ID获取用户信息
class GetUserById(Resource):
    def get(self, userid):
        user = User.query.filter(User.id == userid).first()  # 数据库查询第一个匹配的数据
        return user_schema.dump(user)


api.add_resource(GetUserById, '/manager/userById/<int:userid>')


# 新增用户
class AddUser(Resource):
    def post(self):

        add_username = request.form.get('username')
        add_password = request.form.get('password')
        add_studentnum = request.form.get('studentnum')
        add_email = request.form.get('email')
        add_status = request.form.get('status')
        add_user = User(

            username=add_username,
            password=add_password,
            studentnum=add_studentnum,
            email=add_email,
            status=add_status
        )
        try:
            db.session.add(add_user)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "用户添加失败，数据存在空值或不合法"}
        return {"code": 200,
                "msg": "成功添加用户"}


api.add_resource(AddUser, '/manager/addUser')


# 删除用户
class DeleteUser(Resource):
    def get(self):
        delete_userid = request.args.get('userid')
        delete_user = User.query.filter(User.id == delete_userid).first()
        try:
            db.session.delete(delete_user)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '用户删除失败，不存在该用户'}
        return {"code": 200,
                'msg': '用户删除成功'}


api.add_resource(DeleteUser, '/manager/deleteUser')


# 更新用户信息
class UpdateUser(Resource):
    def post(self):
        update_userid = request.form.get('id')
        update_username = request.form.get('username')
        update_password = request.form.get('password')
        update_studentnum = request.form.get('studentnum')
        update_email = request.form.get('email')
        update_status = request.form.get('status')
        update_user = User.query.filter(User.id == update_userid).first()
        try:
            update_user.username = update_username
            update_user.password = update_password
            update_user.studentnum = update_studentnum
            update_user.email = update_email
            update_user.status = update_status
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "用户信息更新失败，用户不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新用户信息"}


api.add_resource(UpdateUser, '/manager/updateUser')


# 获取该用户借阅的所有书籍
class GetUserBorrowBooks(Resource):
    def get(self, userid):
        user = User.query.filter(User.id == userid).first()
        user_borrows = user.borrowbooks
        return borrows_schema.dump(user_borrows)


api.add_resource(GetUserBorrowBooks, '/manager/userBorrowBooks/<int:userid>')


# ---------------------------图书租收---------------------------------
# 图书租借给用户
class RentBookToUser(Resource):
    # def post(self):
    #     borrow_userid = request.form.get('userid')
    #     borrow_bookid = request.form.get('bookid')
    #     borrow_quantity = request.form.get('quantity')
    #     borrow = Borrow(
    #         userid=borrow_userid,
    #         bookid=borrow_bookid,
    #         quantity=borrow_quantity
    #     )
    #     try:
    #         db.session.add(borrow)
    #         db.session.commit()
    #     except:
    #         db.session.rollback()
    #         return {"code": 500,
    #                 "msg": "书籍借出失败"}
    #     return {"code": 200,
    #             "msg": "成功借出书籍"}
    #
    def post(self):
        borrow_userid = request.form.get('userid')
        borrow_bookid = request.form.get('bookid')
        borrow_quantity = request.form.get('quantity')
        borrow = Borrow(
            userid=borrow_userid,
            bookid=borrow_bookid,
            quantity=borrow_quantity,
        )
        borrowbook = Book.query.filter(Book.id == borrow_bookid).first()
        update_inventory = borrowbook.inventory - int(borrow_quantity)
        user = User.query.filter(User.id == borrow_userid).first()
        already_borrows = Borrow.query.filter(Borrow.userid == borrow_userid).all()
        already_borrowsFormUser = UserBookBorrow.query.filter(UserBookBorrow.userid == borrow_userid).all()
        total_borrowed_books = 0  # 初始化已借的书籍数量为0
        for al_borrow in already_borrows:
            if al_borrow.status == 0:
                total_borrowed_books += al_borrow.quantity  # 逐个增加数量
        for al_borrow2 in already_borrowsFormUser:
            if al_borrow2.status == 0:
                total_borrowed_books += 1  # 逐个增加数量
        print(total_borrowed_books)
        if user.status == 0:  # 学生身份
            if total_borrowed_books + int(borrow_quantity) > 6:
                return {"code": 500,
                        "msg": "借书失败，学生不能借超过6本书"}
            else:
                try:
                    db.session.add(borrow)
                    borrowbook.inventory = update_inventory
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借出书籍"}
        else:  # 教师身份
            if total_borrowed_books + int(borrow_quantity) > 10:
                return {"code": 500,
                        "msg": "借书失败，教师不能借超过10本书"}
            else:
                try:
                    db.session.add(borrow)
                    borrowbook.inventory = update_inventory
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借出书籍"}


api.add_resource(RentBookToUser, '/manager/rentBookToUser')


# 收回图书
class ReceiveBookFromUser(Resource):
    # def post(self):
    #     return_userid = request.form.get('userid')
    #     return_bookid = request.form.get('bookid')
    #     borrow = Borrow.query.filter(Borrow.userid == return_userid, Borrow.bookid == return_bookid).first()
    #     returnbook = Book.query.filter(Book.id == return_bookid).first()
    #     if borrow is None:
    #         return {"code": 500,
    #                 "msg": "收回书籍失败，不存在该借阅记录"}
    #     try:
    #         borrow.returntime = datetime.now()
    #         borrow.status = 1
    #         returnbook.inventory = returnbook.inventory + 1
    #         db.session.commit()
    #         # 判断是否超出30天，若是则进行超一天一元的罚款
    #         borrowtime = borrow.borrowtime
    #         returntime = borrow.returntime
    #         delta = returntime - borrowtime
    #         days = delta.days
    #         money = days
    #         if days > 30:
    #             add_fine = Fine(
    #                 userid=return_userid,
    #                 bookid=return_bookid,
    #                 money=money,
    #                 borrowid=borrow.id
    #             )
    #             try:
    #                 db.session.add(add_fine)
    #                 db.session.commit()
    #             except:
    #                 db.session.rollback()
    #                 return {"code": 500,
    #                         "msg": "罚款信息信息添加失败，数据存在空值或不合法"}
    #             return {"code": 200,
    #                     "msg": "罚款信息添加成功"}
    #     except:
    #         db.session.rollback()
    #         return {"code": 500,
    #                 "borrowid": borrow.id,
    #                 "msg": "收回书籍失败，更新数据库时发生错误"}
    #     return {"code": 200,
    #             "msg": "收回书籍成功"}

    def post(self):
        return_userid = int(request.form.get('userid'))
        return_bookid = int(request.form.get('bookid'))
        borrow = Borrow.query.filter(Borrow.userid == return_userid, Borrow.bookid == return_bookid).first()
        borrow_quantity = borrow.quantity
        if borrow is None:
            return {"code": 500,
                    "msg": "收回书籍失败，不存在该借阅记录"}
        try:
            borrow.returntime = datetime.now()
            borrow.status = 1
            returnbook = Book.query.filter(Book.id == return_bookid).first()
            returnbook.inventory += borrow_quantity
            db.session.commit()
            # 判断是否超出30天，若是则进行超一天一元的罚款
            borrowtime = borrow.borrowtime
            returntime = borrow.returntime
            delta = returntime - borrowtime
            days = delta.days
            money = days * borrow_quantity
            if days > 30:
                add_fine = Fine(
                    userid=return_userid,
                    bookid=return_bookid,
                    money=money,
                    borrowid=borrow.id
                )
                try:
                    db.session.add(add_fine)
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "罚款信息信息添加失败，数据存在空值或不合法"}
                return {"code": 200,
                        "msg": "罚款信息添加成功"}
        except:
            db.session.rollback()
            return {"code": 500,
                    "borrowid": borrow.id,
                    "msg": "收回书籍失败，更新数据库时发生错误"}
        return {"code": 200,
                "msg": "收回书籍成功"}


api.add_resource(ReceiveBookFromUser, '/manager/receiveBookFromUser')


# ---------------------------信息公告---------------------------------
# 获取所有信息公告
class GetAllInformation(Resource):
    def get(self):
        information_all = Information.query.all()
        return informations_schema.dump(information_all)


api.add_resource(GetAllInformation, '/manager/allInformation')


# 获取该管理员发布的信息公告
class GetInfoByManagerId(Resource):
    def get(self, managerid):
        manager = Manager.query.filter(Manager.id == managerid).first()
        information = manager.informations
        return informations_schema.dump(information)


api.add_resource(GetInfoByManagerId, '/manager/infoByManagerId/<int:managerid>')


# 新增公告
class AddInformation(Resource):
    def post(self):
        add_managerid = request.form.get('managerid')
        add_message = request.form.get('message')
        add_info = Information(
            managerid=add_managerid,
            message=add_message
        )
        try:
            db.session.add(add_info)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "公告信息添加失败，数据存在空值或不合法"}
        return {"code": 200,
                "msg": "公告信息添加成功"}


api.add_resource(AddInformation, '/manager/addInformation')


# 删除信息公告
class DeleteInformation(Resource):
    def get(self):
        delete_infoid = request.args.get('infoid')
        delete_info = Information.query.filter(Information.id == delete_infoid).first()
        if delete_info is None:
            return {"code": 500,
                    "msg": "删除信息公告失败，不存在该信息公告"}
        try:
            db.session.delete(delete_info)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '公告信息删除失败，数据库删除数据不成功'}
        return {"code": 200,
                'msg': '信息公告删除成功'}


api.add_resource(DeleteInformation, '/manager/deleteInformation')


# 更新公告信息
class UpdateInformation(Resource):
    def post(self):
        update_infoid = request.form.get('infoid')
        update_managerid = request.form.get('managerid')
        update_message = request.form.get('message')
        update_info = Information.query.filter(Information.id == update_infoid).first()
        if update_info is None:
            return {"code": 500,
                    "msg": "更新信息公告失败，不存在该信息公告"}
        try:
            update_info.managerid = update_managerid
            update_info.message = update_message
            update_info.uploadtime = datetime.now()
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "公告信息更新失败，数据库更新失败"}
        return {"code": 200,
                "msg": "成功更新公告信息"}


api.add_resource(UpdateInformation, '/manager/updateInformation')


# ------------------------------------客服答疑--------------------------------
# 获取所有用户咨询
class AllConsults(Resource):
    def get(self):
        consult_all = UserConsult.query.all()
        return userconsults_schema.dump(consult_all)


api.add_resource(AllConsults, '/manager/allConsults')


# 客服解答
class AnswerConsult(Resource):
    def post(self):
        answer_id = request.form.get('id')
        content = request.form.get('content')
        consult = UserConsult.query.filter(UserConsult.id == answer_id).first()
        try:
            consult.answer = content
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "客服答疑失败"}
        return {"code": 200,
                "msg": "成功答疑问题"}


api.add_resource(AnswerConsult, '/manager/answerConsult')


# 删除咨询
class DeleteConsult(Resource):
    def get(self):
        delete_consultid = request.args.get('consultid')
        delete_consult = UserConsult.query.filter(UserConsult.id == delete_consultid).first()
        try:
            db.session.delete(delete_consult)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '咨询删除失败，不存在该管理员'}
        return {"code": 200,
                'msg': '咨询删除成功'}


api.add_resource(DeleteConsult, '/manager/deleteConsult')


# 个人账号信息
class ManagerInfo(Resource):
    def get(self, managername):
        manager = Manager.query.filter(Manager.managername == managername).first()
        return manager_schema.dump(manager)


api.add_resource(ManagerInfo, '/manager/managerInfo/<string:managername>')


# ---------------------------管理员管理---------------------------------
# 获取所有管理员信息
class GetAllManagers(Resource):
    def get(self):  # get请求
        manager_all = Manager.query.all()
        return managers_schema.dump(manager_all)


api.add_resource(GetAllManagers, '/manager/allManagers')


# 根据管理员ID获取管理员信息
class GetManagerById(Resource):
    def get(self, managerid):
        manager = Manager.query.filter(Manager.id == managerid).first()  # 数据库查询第一个匹配的数据
        return manager_schema.dump(manager)


api.add_resource(GetManagerById, '/manager/managerById/<int:managerid>')


# 新增管理员
class AddManager(Resource):
    def post(self):
        add_managername = request.form.get('managername')
        add_password = request.form.get('password')
        add_email = request.form.get('email')
        add_manager = Manager(
            managername=add_managername,
            password=add_password,
            email=add_email,
        )
        try:
            db.session.add(add_manager)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "管理员添加失败，数据存在空值或不合法"}
        return {"code": 200,
                "msg": "成功添加管理员"}


api.add_resource(AddManager, '/manager/addManager')


# 删除管理员
class DeleteManager(Resource):
    def get(self):
        delete_managerid = request.args.get('managerid')
        delete_manager = Manager.query.filter(Manager.id == delete_managerid).first()
        try:
            db.session.delete(delete_manager)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '管理员删除失败，不存在该管理员'}
        return {"code": 200,
                'msg': '管理员删除成功'}


api.add_resource(DeleteManager, '/manager/deleteManager')


# 更新管理员信息
class UpdateManager(Resource):
    def post(self):
        update_managerid = request.form.get('id')
        update_managername = request.form.get('managername')
        update_password = request.form.get('password')
        update_email = request.form.get('email')
        update_manager = Manager.query.filter(Manager.id == update_managerid).first()
        try:
            update_manager.managername = update_managername
            update_manager.password = update_password
            update_manager.email = update_email
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "管理员信息更新失败，管理员不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新管理员信息"}


api.add_resource(UpdateManager, '/manager/updateManager')


# 获取所有借阅表单
class GetAllBorrows(Resource):
    def get(self):  # get请求
        borrow_all = Borrow.query.all()
        return borrows_schema.dump(borrow_all)


api.add_resource(GetAllBorrows, '/manager/allBorrows')


# 更新用户信息
class ManagerUpdateUser(Resource):
    def post(self):
        update_userid = request.form.get('userid')
        update_username = request.form.get('username')
        update_password = request.form.get('password')
        update_studentnum = request.form.get('studentnum')
        update_email = request.form.get('email')
        update_status = request.form.get('status')
        update_user = Manager.query.filter(User.id == update_userid).first()
        try:
            update_user.username = update_username
            update_user.password = update_password
            update_user.studentnum = update_studentnum
            update_user.email = update_email
            update_user.status = update_status
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "用户信息更新失败，管理员不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新用户信息"}


api.add_resource(ManagerUpdateUser, '/manager/updateUser')


# -------------------------用户端接口-------------------------------
# —————————————————————————图书借还————————————————————————
# 根据userid获取所有借书的书籍信息
class GetBorrowBooksByUserid(Resource):
    def get(self, userid):
        borrows = Borrow.query.filter(Borrow.userid == userid).all()  # 数据库查询第一个匹配的数据
        borrow_books = []
        for borrow in borrows:
            book = Book.query.filter(Book.id == borrow.bookid).first()
            borrow_books.append(book)
        return books_schema.dump(borrow_books)


api.add_resource(GetBorrowBooksByUserid, '/user/borrowBooksByUserid//<int:userid>')


# 根据userid获取所有借阅信息
class GetBorrowByUserid(Resource):
    def get(self, userid):
        borrow = Borrow.query.filter(Borrow.userid == userid).all()  # 数据库查询第一个匹配的数据
        return borrows_schema.dump(borrow)


api.add_resource(GetBorrowByUserid, '/user/borrowByUserid//<int:userid>')


# 根据bookid获取book信息(bookinfo)
class getBookByBookid(Resource):
    def get(self, bookid):
        findBook = Book.query.filter(Book.id == bookid).first()
        return book_schema.dump(findBook)


api.add_resource(getBookByBookid, '/user/getBookByBookid/<int:bookid>')


# 获得图书图片
class getBookImageByBookid(Resource):
    def get(self, bookid):
        findBook = BookImage.query.filter(BookImage.bookid == bookid).first()
        return bookImage_schema.dump(findBook)


api.add_resource(getBookImageByBookid, '/user/bookImageByBookid/<int:bookid>')


# 用户借书
class BorrowBook(Resource):
    def post(self):
        borrow_userid = request.form.get('userid')
        borrow_bookid = request.form.get('bookid')
        borrow_quantity = request.form.get('quantity')
        borrow = Borrow(
            userid=borrow_userid,
            bookid=borrow_bookid,
            quantity=borrow_quantity,
        )
        borrowbook = Book.query.filter(Book.id == borrow_bookid).first()
        update_inventory = borrowbook.inventory - int(borrow_quantity)
        user = User.query.filter(User.id == borrow_userid).first()
        already_borrows = Borrow.query.filter(Borrow.userid == borrow_userid).all()
        already_borrowsFormUser = UserBookBorrow.query.filter(UserBookBorrow.userid == borrow_userid).all()
        total_borrowed_books = 0  # 初始化已借的书籍数量为0
        for al_borrow in already_borrows:
            if al_borrow.status == 0:
                total_borrowed_books += al_borrow.quantity  # 逐个增加数量
        for al_borrow2 in already_borrowsFormUser:
            if al_borrow2.status == 0:
                total_borrowed_books += 1  # 逐个增加数量
        print(total_borrowed_books)
        if user.status == 0:  # 学生身份
            if total_borrowed_books + int(borrow_quantity) > 6:
                return {"code": 500,
                        "msg": "借书失败，学生不能借超过6本书"}
            else:
                try:
                    db.session.add(borrow)
                    borrowbook.inventory = update_inventory
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借出书籍"}
        else:  # 教师身份
            if total_borrowed_books + int(borrow_quantity) > 10:
                return {"code": 500,
                        "msg": "借书失败，教师不能借超过10本书"}
            else:
                try:
                    db.session.add(borrow)
                    borrowbook.inventory = update_inventory
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借出书籍"}


api.add_resource(BorrowBook, '/user/borrowBook')


# 用户还书
class ReturnBook(Resource):
    def post(self):
        return_userid = int(request.form.get('userid'))
        return_bookid = int(request.form.get('bookid'))
        borrow = Borrow.query.filter(Borrow.userid == return_userid, Borrow.bookid == return_bookid).first()
        borrow_quantity = borrow.quantity
        if borrow is None:
            return {"code": 500,
                    "msg": "收回书籍失败，不存在该借阅记录"}
        try:
            borrow.returntime = datetime.now()
            borrow.status = 1
            returnbook = Book.query.filter(Book.id == return_bookid).first()
            returnbook.inventory += borrow_quantity
            db.session.commit()
            # 判断是否超出30天，若是则进行超一天一元的罚款
            borrowtime = borrow.borrowtime
            returntime = borrow.returntime
            delta = returntime - borrowtime
            days = delta.days
            money = days * borrow_quantity
            if days > 30:
                add_fine = Fine(
                    userid=return_userid,
                    bookid=return_bookid,
                    money=money,
                    borrowid=borrow.id
                )
                try:
                    db.session.add(add_fine)
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "罚款信息信息添加失败，数据存在空值或不合法"}
                return {"code": 200,
                        "msg": "罚款信息添加成功"}
        except:
            db.session.rollback()
            return {"code": 500,
                    "borrowid": borrow.id,
                    "msg": "还书失败，更新数据库时发生错误"}
        return {"code": 200,
                "msg": "还书成功"}


api.add_resource(ReturnBook, '/user/returnBook')


# —————————————————————————图书互借————————————————————————
# 获取所有用户书籍信息
class GetAllUserBooks(Resource):
    def get(self):
        userbook_all = UserBook.query.all()
        return userBooks_schema.dump(userbook_all)


api.add_resource(GetAllUserBooks, '/user/allUserBooks')


# 用户上传图书
class AddUserBook(Resource):
    def post(self):
        add_userid = request.form.get('userid')
        add_bookname = request.form.get('bookname')
        add_author = request.form.get('author')
        add_kind = request.form.get('kind')
        add_inventory = request.form.get('inventory')
        add_book = UserBook(
            userid=add_userid,
            bookname=add_bookname,
            author=add_author,
            kind=add_kind,
            inventory=add_inventory
        )
        try:
            db.session.add(add_book)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "图书到userbooks添加失败，数据存在空值或不合法"}
        return {"code": 200,
                "msg": "成功添加图书到userbooks"}


api.add_resource(AddUserBook, '/user/addUserBook')


# 获取所有用户借出的图书信息
class GetAllUserBookBorrows(Resource):
    def get(self):  # get请求
        user_book_borrow_all = UserBookBorrow.query.all()
        return userBookBorrows_schema.dump(user_book_borrow_all)


api.add_resource(GetAllUserBookBorrows, '/user/allUserBookBorrows')


class BorrowUserBook(Resource):
    def post(self):
        borrow_userid = int(request.form.get('userid'))
        borrow_borrowuserid = int(request.form.get('borrowuserid'))  # 被借的
        borrow_bookname = request.form.get('bookname')
        borrow_bookauthor = request.form.get('author')
        borrow_bookkind = request.form.get('kind')

        userbookborrow = UserBookBorrow(
            userid=borrow_userid,
            borrowuserid=borrow_borrowuserid,
            bookname=borrow_bookname,
            author=borrow_bookauthor,
            kind=borrow_bookkind
        )
        user = User.query.filter(User.id == borrow_userid).first()
        already_borrows = Borrow.query.filter(Borrow.userid == borrow_userid).all()
        already_borrowsFormUser = UserBookBorrow.query.filter(UserBookBorrow.userid == borrow_userid).all()
        borrowuserbook = UserBook.query.filter(UserBook.userid == borrow_borrowuserid,
                                               UserBook.bookname == borrow_bookname).first()
        total_borrowed_books = 0  # 初始化已借的书籍数量为0
        for al_borrow in already_borrows:
            if al_borrow.status == 0:
                total_borrowed_books += al_borrow.quantity  # 逐个增加数量
        for al_borrow2 in already_borrowsFormUser:
            if al_borrow2.status == 0:
                total_borrowed_books += 1  # 逐个增加数量
        print(total_borrowed_books)
        for al_borrow in already_borrows:
            if al_borrow.status == 0:
                total_borrowed_books += al_borrow.quantity  # 逐个增加数量
        if user.status == 0:  # 学生身份
            if total_borrowed_books + 1 > 6:
                return {"code": 500,
                        "msg": "借书失败，学生不能借超过6本书"}
            else:
                try:
                    db.session.add(userbookborrow)
                    borrowuserbook.inventory -= 1
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借书"}
        else:  # 教师身份
            if total_borrowed_books + 1 > 10:
                return {"code": 500,
                        "msg": "借书失败，教师不能借超过10本书"}
            else:
                try:
                    db.session.add(userbookborrow)
                    borrowuserbook.inventory -= 1
                    db.session.commit()
                except:
                    db.session.rollback()
                    return {"code": 500,
                            "msg": "书籍借出失败"}
                return {"code": 200,
                        "msg": "成功借书"}


api.add_resource(BorrowUserBook, '/user/borrowUserBook')


# 用户还书
class ReturnUserBook(Resource):
    def post(self):
        return_userid = int(request.form.get('userid'))
        return_borrowuserid = int(request.form.get('borrowuserid'))
        return_bookname = request.form.get('bookname')
        userbookborrow = UserBookBorrow.query.filter(UserBookBorrow.userid == return_userid,
                                                     UserBookBorrow.borrowuserid == return_borrowuserid,
                                                     UserBookBorrow.bookname == return_bookname).first()
        if userbookborrow is None:
            return {"code": 500,
                    "msg": "还书失败，不存在该借阅记录"}
        try:
            userbookborrow.returntime = datetime.now()
            userbookborrow.status = 1
            returnuserbook = UserBook.query.filter(UserBook.userid == return_borrowuserid,
                                                   UserBook.bookname == return_bookname).first()
            returnuserbook.inventory += 1
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "userbookborrowid": userbookborrow.id,
                    "msg": "还书失败，更新数据库时发生错误"}
        return {"code": 200,
                "msg": "还书成功"}


api.add_resource(ReturnUserBook, '/user/returnUserBook')


# —————————————————————————来信咨询————————————————————————

# 查看所有咨询
class allConsult(Resource):
    def get(self):
        consult_all = UserConsult.query.all()
        return userconsults_schema.dump(consult_all)


api.add_resource(allConsult, '/user/allConsult')


# 根据userid查看咨询
class myConsult(Resource):
    def get(self, userid):
        myConsult = UserConsult.query.filter(UserConsult.userid == userid).all()
        return userconsults_schema.dump(myConsult)


api.add_resource(myConsult, '/user/myConsult//<int:userid>')


# 新增咨询
class addUserConsult(Resource):
    def post(self):
        email = request.form.get('email')
        consultUser = User.query.filter(User.email == email).first()
        add_userid = consultUser.id
        print(add_userid)
        add_consult = request.form.get('content')

        add_consult = UserConsult(
            userid=add_userid,
            consult=add_consult,
        )
        try:
            db.session.add(add_consult)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "咨询发送失败"}
        return {"code": 200,
                "msg": "咨询发送成功"}


api.add_resource(addUserConsult, '/user/addUserConsult')


# —————————————————————————读书笔记————————————————————————
class allBooknote(Resource):
    def get(self):
        booknote_all = BookNote.query.all()
        return booknotes_schema.dump(booknote_all)


api.add_resource(allBooknote, '/user/allBooknote')


class myBooknote(Resource):
    def get(self, userid):
        myBooknote = BookNote.query.filter(BookNote.userid == userid).all()  # 数据库查询第一个匹配的数据
        return booknotes_schema.dump(myBooknote)


api.add_resource(myBooknote, '/user/myBooknote/<int:userid>')


# 添加新笔记（需要userid bookname note time）
class addmyBooknote(Resource):
    def post(self):
        add_note = request.form.get('note')
        add_userid = request.form.get('userid')
        add_bookname = request.form.get('bookname')
        add_bookid = Book.query.filter(Book.bookname == add_bookname).first().id
        add_notetime = request.form.get('time')

        add_booknote = BookNote(
            userid=add_userid,
            bookid=add_bookid,
            note=add_note,
            notetime=add_notetime
        )
        try:
            db.session.add(add_booknote)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "新笔记添加失败"}
        return {"code": 200,
                "msg": "新笔记添加成功"}


api.add_resource(addmyBooknote, '/user/addmyBooknote')


# 删除笔记
class deleteBooknote(Resource):
    def get(self):
        delete_noteid = request.args.get('noteid')
        delete_note = BookNote.query.filter(BookNote.id == delete_noteid).first()
        # print(delete_note)
        try:
            db.session.delete(delete_note)
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    'msg': '读书笔记删除失败，不存在该笔记'}
        return {"code": 200,
                'msg': '读书笔记删除成功'}


api.add_resource(deleteBooknote, '/user/deleteBooknote')


# 更新已有笔记
class updatemyBooknote(Resource):
    def post(self):
        update_noteid = request.form.get('noteid')
        update_note = request.form.get('note')
        update_notetime = request.form.get('notetime')
        update_booknote = BookNote.query.filter(BookNote.id == update_noteid).first()
        try:
            update_booknote.note = update_note
            update_booknote.notetime = update_notetime
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "笔记更新失败，笔记不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新该笔记"}


api.add_resource(updatemyBooknote, '/user/updatemyBooknote')


# —————————————————————————个人账号————————————————————————
# 个人信息
class myInfo(Resource):
    def get(self, username):
        user = User.query.filter(User.username == username).first()
        return user_schema.dump(user)


api.add_resource(myInfo, '/user/myInfo/<string:username>')


# 更新个人信息
class updateUserinfo(Resource):
    def post(self):
        update_userid = request.form.get('id')
        print(update_userid)
        update_username = request.form.get('username')
        update_password = request.form.get('password')
        update_email = request.form.get('email')
        update_user = User.query.filter(User.id == update_userid).first()
        try:
            update_user.username = update_username
            update_user.password = update_password
            update_user.email = update_email
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "用户信息更新失败，用户不存在或数据不合法"}
        return {"code": 200,
                "msg": "成功更新用户信息"}


api.add_resource(updateUserinfo, '/user/updateUserinfo')


# 罚款缴费
class PayFine(Resource):
    def get(self):
        pay_userid = request.form.get('userid')
        pay_bookid = request.form.get('bookid')
        pay_fine = Fine.query.filter(Fine.userid == pay_userid, Fine.bookid == pay_bookid).first()
        if pay_fine is None:
            return {"code": 500,
                    "msg": "用户缴费失败，不存在该罚单"}
        if pay_fine.status == 1:
            return {"code": 500,
                    "msg": "用户缴费失败，该罚单已被支付"}
        try:
            pay_fine.status = 1
            db.session.commit()
        except:
            db.session.rollback()
            return {"code": 500,
                    "msg": "用户缴费失败，数据库数据处理出现问题"}
        return {"code": 200,
                "msg": "用户缴费成功"}


api.add_resource(PayFine, '/user/payFine')


# ------------------------管理系统接口------------------------------
# 登录
class Login(Resource):
    def post(self):
        login_name = request.form.get('username')
        login_password = request.form.get('password')
        print(login_name + "," + login_password)
        user = User.query.filter(User.username == login_name).first()
        manager = Manager.query.filter(Manager.managername == login_name).first()
        if user is not None:
            if login_password == user.password:
                return {"code": 201,
                        "msg": "用户登陆成功"}
            else:
                return {"code": 500,
                        "msg": "用户密码错误"}
        if manager is not None:
            if login_password == manager.password:
                return {"code": 202,
                        "msg": "管理员登陆成功"}
            else:
                return {"code": 500,
                        "msg": "管理员密码错误"}


api.add_resource(Login, '/system/login')


# 图书推荐
class GetRecommendBooks(Resource):
    def get(self):
        # 定义SQL查询
        query = text("""
            SELECT bookid, COUNT(*) as borrow_count
            FROM borrow
            GROUP BY bookid
            ORDER BY borrow_count DESC
            LIMIT 10;
        """)

        # 执行查询并检索结果
        result = db.session.execute(query)

        # 遍历结果并检索相应的书籍对象
        reco_books = []
        for row in result:
            book = Book.query.filter(Book.id == row[0]).first()
            reco_books.append(book)

        # 返回推荐书籍列表
        return books_schema.dump(reco_books)


api.add_resource(GetRecommendBooks, '/system/recommendBooks')


# 违规处罚
class GetAllFines(Resource):
    def get(self):  # get请求
        fine_all = Fine.query.all()
        return fines_schema.dump(fine_all)


api.add_resource(GetAllFines, '/manager/allFines')


# 在线阅读
class GetOnlineBooks(Resource):
    def get(self):
        online_books = OnlineBook.query.all()
        return onlinebooks_schema.dump(online_books)


api.add_resource(GetOnlineBooks, '/system/allOnlineBooks')


# 根据书籍id获取在线阅读书籍的文本内容
class GetOnlineBookById(Resource):
    def get(self):
        bookid = request.args.get('bookid')
        online_book = OnlineBook.query.filter(OnlineBook.id == bookid).first()
        return onlinebook_schema.dump(online_book)


api.add_resource(GetOnlineBookById, '/system/onlineBookById')



# 图书报表处理
# 获取热门书籍
class GetHotBooks(Resource):
    def get(self):
        # 定义SQL查询
        query = text("""
            SELECT bookid, COUNT(*) as borrow_count
            FROM borrow
            GROUP BY bookid
            HAVING borrow_count > 1
        """)

        result = db.session.execute(query)

        # 遍历结果并检索相应的书籍对象
        hot_books = []
        for row in result:
            book = Book.query.filter(Book.id == row[0]).first()
            hot_books.append(book)

        # 返回热门书籍
        return books_schema.dump(hot_books)


api.add_resource(GetHotBooks, '/manager/allHotBooks')


# 获取热门用户
class GetHotUsers(Resource):
    def get(self):
        # 定义SQL查询
        query = text("""
            SELECT userid, COUNT(*) as borrow_count
            FROM borrow
            GROUP BY userid
            HAVING borrow_count > 1
        """)

        result = db.session.execute(query)

        # 遍历结果并检索相应的书籍对象
        hot_users = []
        for row in result:
            user = User.query.filter(User.id == row[0]).first()
            hot_users.append(user)

        # 返回热门书籍
        return users_schema.dump(hot_users)


api.add_resource(GetHotUsers, '/manager/allHotUsers')

if __name__ == '__main__':
    # 这种方式开启服务，既可以通过http: // 127.0.0.1: 9999在本地浏览器访问，
    # 也可以通过http: // 192.68.0.1: 9999在本地浏览器访问；
    # 同时同局域网内其他用户也可以通过http: // 192.68.0.1: 9999在各自PC的浏览器访问，这是不是有点互联网的意思了~
    app.run(host='0.0.0.0', port=5000)
